/**
 *  Copyright 2010 ShenYang voole Co. Ltd.
 *  All right reserved. 
 */
package cn.piaoyi.dao.hibernate;


import java.sql.SQLException;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import cn.piaoyi.bean.Goods;
import cn.piaoyi.comm.Constant;
import cn.piaoyi.dao.GoodsDAO;

/**
 * 功能描述:管理员操作实现
 *
 * 创建日期 Apr 10, 2010
 *
 * @author zhangbaochao
 *
 * @version 1.0
 *
 */
public class GoodsHibernateDAO extends HibernateDaoSupport implements GoodsDAO {

	private static final String LOAD_BY_ID = "from Goods where id = ?";
	private static final String  REMOVE_BY_ID = "delete from Goods where id = ?";
	
	/**
	 * 保存Goods对象
	 * 
	 * @param goods
	 * 			Goods对象
	 * @return Goods
	 * 			Goods对象
	 */
	public Goods saveGoods(Goods goods){
		this.getHibernateTemplate().save(goods);
		return goods;
	}
	/**
	 * 保存或更新Goods对象
	 * 
	 * @param goods
	 * 			Goods对象
	 * @return Goods
	 * 			Goods对象
	 */
	public Goods saveOrUpdateGoods(Goods goods){
		this.getHibernateTemplate().saveOrUpdate(goods);
		return goods;
	}
	/**
	 * 保存Goods对象
	 * 
	 * @param goods
	 * 			Goods对象
	 * @return Goods
	 * 			Goods对象
	 */
	public Goods updateGoods(Goods goods){
		this.getHibernateTemplate().update(goods);
		return goods;
	}
	
	/**
	 * 根据主键ID获取Goods对象
	 * 
	 * @param id
	 * 			主键ID
	 * @return Goods
	 * 			Goods对象
	 */
	public Goods findGoodsById(long id){
		List l = this.getHibernateTemplate().find(LOAD_BY_ID,id);
		if(l == null || l.isEmpty()){
			return null;
		}else{
			return (Goods) l.get(0);
		}
	}
	
	/**
	 * 获取Goods对象个数
	 * 
	 * @param categoryId
	 * 			long
	 * @return long
	 * 			Goods对象个数
	 */
	public long getGoodsNum(final int cityId,final long categoryId,final String name,final int isShow,final int state){
		List l = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException {
				Criteria c = s.createCriteria(Goods.class);
				c.setProjection(Projections.count("id"));
				if (cityId != -1) {
					c.add(Restrictions.eq("cityId", new Integer(cityId)));
				}
				if (categoryId != -1) {
					c.add(Restrictions.eq("categoryId", new Long(categoryId)));
				}
				if(StringUtils.isNotBlank(name)){
					c.add(Restrictions.like("name", "%" + name + "%"));
				}
				if (isShow != -1) {
					c.add(Restrictions.eq("isShow", new Integer(isShow)));
				}
				if (state != -1) {
					c.add(Restrictions.eq("state", new Integer(state)));
				}
				return c.list();
			}
		});
		if (l == null || l.isEmpty()) {
			return 0;
		} else {
			return ((Integer) l.get(0)).longValue();
		}
	}
	
	public long getGoodsNum(final int cityId,final long categoryId,final List ids,final String name,final int isShow,final int state,final int time,final String startTime,final String endTime){
		List l = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException {
				Criteria c = s.createCriteria(Goods.class);
				c.setProjection(Projections.count("id"));
				if (cityId != -1) {
					c.add(Restrictions.eq("cityId", new Integer(cityId)));
				}
				if (categoryId != -1) {
					c.add(Restrictions.eq("categoryId", new Long(categoryId)));
				}
				if(StringUtils.isNotBlank(name)){
					c.add(Restrictions.like("name", "%" + name + "%"));
				}
				if (isShow != -1) {
					c.add(Restrictions.eq("isShow", new Integer(isShow)));
				}
				if (state != -1) {
					c.add(Restrictions.eq("state", new Integer(state)));
				}
				if(time != -1){
					c.add(Restrictions.sqlRestriction("((TO_DAYS(startTime) - TO_DAYS(NOW()) <="+time+") and (TO_DAYS(endTime) - TO_DAYS(NOW( )) >="+time+"))"));
				}
				if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
					c.add(Restrictions.sqlRestriction("(not((TO_DAYS(startTime) - TO_DAYS('"+startTime+"') >0) and (TO_DAYS(startTime) - TO_DAYS('"+endTime+"') >0))) and (not((TO_DAYS(endTime) - TO_DAYS('"+startTime+"') <0) and (TO_DAYS(endTime) - TO_DAYS('"+endTime+"') <0)))"));
				}
				if(ids != null && !ids.isEmpty()){
					c.add(Restrictions.in("id", ids));
				}
				return c.list();
			}
		});
		if (l == null || l.isEmpty()) {
			return 0;
		} else {
			return ((Integer) l.get(0)).longValue();
		}
	}
	
	/**
	 * 获取UserInfo列表
	 * 
	 * @param categoryId
	 * 			long
	 * @param name
	 * 			String
	 * @param isShow
	 * 			int
	 * @param state
	 * 			int
	 * @param orderby
	 *            String
	 * @param ascOrDesc
	 *            int
	 * @param firstResult
	 *            int
	 * @param maxResults
	 *            int
	 * @return List
	 * 			Goods对象列表
	 */
	public List findGoodses(final int cityId,final long categoryId,final String name,final int isShow,final int state,final String orderby, final int ascOrDesc, final int firstResult, final int maxResults){
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException {
				Criteria c = s.createCriteria(Goods.class);
				if (cityId != -1) {
					c.add(Restrictions.eq("cityId", new Integer(cityId)));
				}
				if (categoryId != -1) {
					c.add(Restrictions.eq("categoryId", new Long(categoryId)));
				}
				if(StringUtils.isNotBlank(name)){
					c.add(Restrictions.like("name", "%" + name + "%"));
				}
				if (isShow != -1) {
					c.add(Restrictions.eq("isShow", new Integer(isShow)));
				}
				if (state != -1) {
					c.add(Restrictions.eq("state", new Integer(state)));
				}
				if (StringUtils.isNotBlank(orderby)) {
					if (ascOrDesc == Constant.ORDER_ASC) {
						c.addOrder(Order.asc(orderby));
					}
					if (ascOrDesc == Constant.ORDER_DESC) {
						c.addOrder(Order.desc(orderby));
					}
				}
				c.setFirstResult(firstResult);
				c.setMaxResults(maxResults);
				return c.list();
			}
		});
	}
	
	public List findGoodses(final int cityId,final long categoryId,final List ids,final String name,final int isShow,final int state,final int time,final String startTime,final String endTime,final String orderby, final int ascOrDesc, final int firstResult, final int maxResults){
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException {
				Criteria c = s.createCriteria(Goods.class);
				if (cityId != -1) {
					c.add(Restrictions.eq("cityId", new Integer(cityId)));
				}
				if (categoryId != -1) {
					c.add(Restrictions.eq("categoryId", new Long(categoryId)));
				}
				if(StringUtils.isNotBlank(name)){
					c.add(Restrictions.like("name", "%" + name + "%"));
				}
				if (isShow != -1) {
					c.add(Restrictions.eq("isShow", new Integer(isShow)));
				}
				if (state != -1) {
					c.add(Restrictions.eq("state", new Integer(state)));
				}
				if(time != -1){
					c.add(Restrictions.sqlRestriction("((TO_DAYS(startTime) - TO_DAYS(NOW()) <="+time+") and (TO_DAYS(endTime) - TO_DAYS(NOW( )) >="+time+"))"));
				}
				if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
					c.add(Restrictions.sqlRestriction("(not((TO_DAYS(startTime) - TO_DAYS('"+startTime+"') >0) and (TO_DAYS(startTime) - TO_DAYS('"+endTime+"') >0))) and (not((TO_DAYS(endTime) - TO_DAYS('"+startTime+"') <0) and (TO_DAYS(endTime) - TO_DAYS('"+endTime+"') <0)))"));
				}
				if(ids != null && !ids.isEmpty()){
					c.add(Restrictions.in("id", ids));
				}
				if (StringUtils.isNotBlank(orderby)) {
					if (ascOrDesc == Constant.ORDER_ASC) {
						c.addOrder(Order.asc(orderby));
					}
					if (ascOrDesc == Constant.ORDER_DESC) {
						c.addOrder(Order.desc(orderby));
					}
				}
				c.setFirstResult(firstResult);
				c.setMaxResults(maxResults);
				return c.list();
			}
		});
	}
	
	/**
	 * 获取UserInfo列表
	 * 
	 * @param orderby
	 *            String
	 * @param ascOrDesc
	 *            int
	 * @return List
	 * 			Goods对象列表
	 */
	public List findGoodses(final String orderby, final int ascOrDesc){
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException {
				Criteria c = s.createCriteria(Goods.class);
				if (StringUtils.isNotBlank(orderby)) {
					if (ascOrDesc == Constant.ORDER_ASC) {
						c.addOrder(Order.asc(orderby));
					}
					if (ascOrDesc == Constant.ORDER_DESC) {
						c.addOrder(Order.desc(orderby));
					}
				}
				return c.list();
			}
		});
	}
	
	/**
	 * 删除Goods对象
	 */
	public void removeGoodsById(final long id){
	    getHibernateTemplate().execute(new HibernateCallback() {
	        public Object doInHibernate(Session s) throws HibernateException, SQLException {
	          Query query = s.createQuery(REMOVE_BY_ID);
	          query.setLong(0, id);
	          query.executeUpdate();
	          return null;
	        }
	      });
	}
}
